package com.example.dao;

import com.example.model.Employee;
import com.example.util.DBUtil;

import java.sql.*;

import java.util.ArrayList;
import java.util.List;

public class EmployeeDAO {
    // 添加员工
    public void addEmployee(Employee emp) throws SQLException {
        String sql = "INSERT INTO employees (name, gender, age, dept_id, position) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, emp.getName());
            stmt.setString(2, emp.getGender());
            stmt.setInt(3, emp.getAge());
            stmt.setInt(4, emp.getDeptId());
            stmt.setString(5, emp.getPosition());
            stmt.executeUpdate();
        }
    }

    // 获取所有员工 (带分页)
    public List<Employee> getAllEmployees(int page, int recordsPerPage) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        int start = (page - 1) * recordsPerPage;

        String sql = "SELECT e.*, d.dept_name FROM employees e " +
                "JOIN departments d ON e.dept_id = d.dept_id " +
                "ORDER BY e.id DESC LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, start);
            stmt.setInt(2, recordsPerPage);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Employee emp = new Employee();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setGender(rs.getString("gender"));
                    emp.setAge(rs.getInt("age"));
                    emp.setDeptId(rs.getInt("dept_id"));
                    emp.setDeptName(rs.getString("dept_name"));
                    emp.setPosition(rs.getString("position"));
                    employees.add(emp);
                }
            }
        }
        return employees;
    }

    // 获取员工总数
    public int getTotalEmployees() throws SQLException {
        String sql = "SELECT COUNT(*) AS total FROM employees";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt("total");
            }
            return 0;
        }
    }

    // 获取所有员工（用于工资管理中的选择）
    public List<Employee> getAllEmployees() throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT id, name FROM employees ORDER BY name";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                employees.add(emp);
            }
        }
        return employees;
    }
    // 更新员工信息
    public void updateEmployee(Employee emp) throws SQLException {
        String sql = "UPDATE employees SET name=?, gender=?, age=?, dept_id=?, position=?  WHERE id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, emp.getName());
            stmt.setString(2, emp.getGender());
            stmt.setInt(3, emp.getAge());
            stmt.setInt(4, emp.getDeptId());
            stmt.setString(5, emp.getPosition());
            stmt.setInt(6, emp.getId());
            stmt.executeUpdate();
        }
    }

    // 删除员工
    public void deleteEmployee(int id) throws SQLException {
        String sql = "DELETE FROM employees WHERE id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }

    // 根据ID获取员工
    public Employee getEmployeeById(int id) throws SQLException {
        String sql = "SELECT e.*, d.dept_name FROM employees e " +
                "JOIN departments d ON e.dept_id = d.dept_id " +
                "WHERE e.id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Employee emp = new Employee();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setGender(rs.getString("gender"));
                    emp.setAge(rs.getInt("age"));
                    emp.setDeptId(rs.getInt("dept_id"));
                    emp.setDeptName(rs.getString("dept_name"));
                    emp.setPosition(rs.getString("position"));
                    return emp;
                }
            }
        }
        return null;
    }
}
